import json

import openpyxl

class Excelhandler():
    def __init__(self,file_path):
        self.file_path = file_path

        # 打开表格
    def open_sheet(self,sheet_name):
        file = r'E:\lemonpython\basic\test.xlsx'
        wb = openpyxl.load_workbook(self.file_path)
        sheet = wb[sheet_name]
        wb.close()
        # print(sheet)
        return sheet
    # 表头
    def title(self,sheet_name):
        # 打开表格
        sheet = self.open_sheet(sheet_name)
        head = sheet[1]
        list=[]
        for i in head:
            list.append(i.value)
        return list
    # 返回所有行
    def all_rows(self,sheet_name):
        title = self.title(sheet_name)

        sheet = self.open_sheet(sheet_name)
        sheet_rows = list(sheet.rows)
        # 得到每一行'
        all_list = []
        for rows in sheet_rows[1:]:
            all_cell=[]
            for cell in rows:
                all_cell.append(cell.value)

            list_dict = dict(zip(title,all_cell))
            all_list.append(list_dict)
        return all_list

    # 写入
    @staticmethod
    def write_excel(file_path,sheet_name,row,column,data):
        wb = openpyxl.load_workbook(file_path)
        sheet = wb[sheet_name]
        sheet.cell(row,column).value = data

        wb.save(file_path)
        wb.close()
if __name__ == '__main__':

    excel = Excelhandler(r'E:\lemonpython\basic\class23_framework\data\test.xlsx')
    excel.open_sheet('register')
    title = excel.title('register')
    ex=excel.all_rows('register')
    ex1 = json.loads(excel.all_rows('register')[2]['data'])
    ex = (excel.all_rows('register')[0]['expect'])
    # js = json.load(ex)
    # all = json.loads(excel.all_rows('register')[0]['expect'])
    # ex = ex.replace('#new_phone#',"asd")

    print((ex))